SELECT dem.demographic_no AS DemNo, count(*) AS 'Appts', dem.patient_status AS Status,
CONCAT( "", dem.last_name , "" ) as "Surname/eChart",
CONCAT( "" , dem.first_name, "" ) As "First Name/Demographic link",
dem.email AS Email, dem.address AS Address, dem.city AS City, dem.province AS Province, dem.postal AS PC, dem.phone AS Phone,
DATE_FORMAT(app.appointment_date,'%d/%m/%Y') AS Date,
prov.last_name AS Doctor
FROM appointment app
LEFT JOIN demographic dem ON app.demographic_no = dem.demographic_no
LEFT JOIN provider prov ON app.provider_no = prov.provider_no
WHERE app.appointment_date >= '{start}' and app.appointment_date <= '{finish}'
AND prov.ohip_no = IF('{provider}'='ALL', prov.ohip_no , '{provider}' )
AND app.demographic_no > 0
GROUP BY app.demographic_no
ORDER BY app.appointment_date, app.provider_no ;
( SELECT ohip_no, CONCAT(first_name,' ',last_name,'(',ohip_no,')') AS name
FROM provider where provider_type='doctor' and status=1 and ohip_no!='')
UNION
( SELECT 'ALL', ' All providers' )
ORDER BY name ;